import mysql.connector
from mysql.connector import Error

def update_database():
    connection = None
    try:
        # 连接到MySQL服务器和数据库
        connection = mysql.connector.connect(
            host='ip',
            port=port,
            user='root',
            password='password',
            database='erp_db'
        )
        
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 创建产品表
            create_product_table = """
            CREATE TABLE IF NOT EXISTS product (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                code VARCHAR(50) NOT NULL COMMENT '产品编码',
                name VARCHAR(100) NOT NULL COMMENT '产品名称',
                specification VARCHAR(200) COMMENT '规格型号',
                unit VARCHAR(20) COMMENT '单位',
                category VARCHAR(50) COMMENT '产品分类',
                description TEXT COMMENT '产品描述',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                is_deleted TINYINT(1) DEFAULT 0,
                UNIQUE KEY uk_code (code)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='产品表'
            """
            cursor.execute(create_product_table)
            print("产品表创建成功")
            
            # 创建物料清单表
            create_bom_table = """
            CREATE TABLE IF NOT EXISTS bom (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                product_id BIGINT NOT NULL COMMENT '产品ID',
                material_id BIGINT NOT NULL COMMENT '物料ID',
                quantity DECIMAL(15,6) NOT NULL COMMENT '用量',
                unit VARCHAR(20) COMMENT '单位',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                is_deleted TINYINT(1) DEFAULT 0,
                KEY idx_product_id (product_id),
                KEY idx_material_id (material_id),
                CONSTRAINT fk_bom_product FOREIGN KEY (product_id) REFERENCES product (id),
                CONSTRAINT fk_bom_material FOREIGN KEY (material_id) REFERENCES product (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='物料清单表'
            """
            cursor.execute(create_bom_table)
            print("物料清单表创建成功")
            
            # 创建生产订单表
            create_production_order_table = """
            CREATE TABLE IF NOT EXISTS production_order (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                order_no VARCHAR(50) NOT NULL COMMENT '生产订单号',
                product_id BIGINT NOT NULL COMMENT '产品ID',
                quantity DECIMAL(15,6) NOT NULL COMMENT '生产数量',
                unit VARCHAR(20) COMMENT '单位',
                planned_start_date DATE COMMENT '计划开始日期',
                planned_end_date DATE COMMENT '计划结束日期',
                actual_start_date DATETIME COMMENT '实际开始时间',
                actual_end_date DATETIME COMMENT '实际结束时间',
                status ENUM('DRAFT','APPROVED','IN_PROGRESS','COMPLETED','CANCELLED') DEFAULT 'DRAFT' COMMENT '状态',
                created_by BIGINT NOT NULL COMMENT '创建人',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                is_deleted TINYINT(1) DEFAULT 0,
                UNIQUE KEY uk_order_no (order_no),
                KEY idx_product_id (product_id),
                KEY idx_status (status),
                CONSTRAINT fk_production_order_product FOREIGN KEY (product_id) REFERENCES product (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='生产订单表'
            """
            cursor.execute(create_production_order_table)
            print("生产订单表创建成功")
            
    except Error as e:
        print(f"更新数据库时出错: {e}")
        
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL连接已关闭")

if __name__ == "__main__":
    update_database()